﻿//
// (C) Copyright 2009 Irantha Suwandarathna (irantha@gmail.com)
// All rights reserved.
//

using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using EffiProzDB;
using EffiProzDB.Lib;

namespace EffiProzDBTest
{
    /// <summary>
    /// Summary description for ConstraintTest
    /// </summary>
    [TestClass]
    public class ConstraintTest
    {
        SessionInterface sessionProxy;
        String user = "sa";
        String password = "";
        String connType = DatabaseURL.S_MEM;
        //String host = "localhost";
        //int port = 0;
        //String path = "Test";
        String database = "Test";
        // string sql = "";
        HsqlProperties props = new HsqlProperties();

        public ConstraintTest()
        {
            sessionProxy = DatabaseManager.newSession(connType, database,
                      user, password, props); 
        }

        private TestContext testContextInstance;

        /// <summary>
        ///Gets or sets the test context which provides
        ///information about and functionality for the current test run.
        ///</summary>
        public TestContext TestContext
        {
            get
            {
                return testContextInstance;
            }
            set
            {
                testContextInstance = value;
            }
        }

        #region Additional test attributes
        //
        // You can use the following additional attributes as you write your tests:
        //
        // Use ClassInitialize to run code before running the first test in the class
        // [ClassInitialize()]
        // public static void MyClassInitialize(TestContext testContext) { }
        //
        // Use ClassCleanup to run code after all tests in a class have run
        // [ClassCleanup()]
        // public static void MyClassCleanup() { }
        //
        // Use TestInitialize to run code before running each test 
        // [TestInitialize()]
        // public void MyTestInitialize() { }
        //
        // Use TestCleanup to run code after each test has run
        // [TestCleanup()]
        // public void MyTestCleanup() { }
        //
        #endregion

        [TestMethod]
        public void TestPrimaryKeySingle()
        {

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32), col3 int,"+
                " PRIMARY KEY (col1) );"; 

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 0;
            string col2val = "test1";
            int col3val = 4;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val,col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResult(resultIn, col1val, col2val, col3val);

            col1val = 1;
            col2val = "test2";
            col3val = 5;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);
            TestHelper.MatchSingleResultRow(resultIn,1, col1val, col2val, col3val);


            col1val = 1;  // primary key violation
            col2val = "test6";
            col3val = 3;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, sql);

        }

      

        [TestMethod]
        public void TestPrimaryKeyMulti()
        {

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32), col3 int," +
                " PRIMARY KEY (col1, col2) );";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 0;
            string col2val = "test1";
            int col3val = 4;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResult(resultIn, col1val, col2val, col3val);

            col1val = 1;
            col2val = "test1";
            col3val = 5;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);
            TestHelper.MatchSingleResultRow(resultIn, 1, col1val, col2val, col3val);


            col1val = 1;  // primary key violation
            col2val = "test1";
            col3val = 3;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, sql);
        }

        [TestMethod]
        public void TestPrimaryKeyMultiSeperated()
        {

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32), col3 int," +
                " PRIMARY KEY (col1, col3) );";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 0;
            string col2val = "test1";
            int col3val = 4;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResult(resultIn, col1val, col2val, col3val);

            col1val = 1;
            col2val = "test1";
            col3val = 5;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);
            TestHelper.MatchSingleResultRow(resultIn, 1, col1val, col2val, col3val);


            col1val = 1;  // primary key violation
            col2val = "test7";
            col3val = 5;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, sql);
        }

        [TestMethod]
        public void TestUniqueKeySingle()
        {

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32), col3 int," +
                " UNIQUE (col1) );";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 0;
            string col2val = "test1";
            int col3val = 4;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResult(resultIn, col1val, col2val, col3val);

            col1val = 1;
            col2val = "test2";
            col3val = 5;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);
            TestHelper.MatchSingleResultRow(resultIn, 1, col1val, col2val, col3val);


            col1val = 1;  // primary key violation
            col2val = "test6";
            col3val = 3;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, sql);

        }

        [TestMethod]
        public void TestUniqueKeyMulti()
        {

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32), col3 int," +
                " UNIQUE (col1, col2) );";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 0;
            string col2val = "test1";
            int col3val = 4;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResult(resultIn, col1val, col2val, col3val);

            col1val = 1;
            col2val = "test1";
            col3val = 5;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);
            TestHelper.MatchSingleResultRow(resultIn, 1, col1val, col2val, col3val);


            col1val = 1;  // primary key violation
            col2val = "test1";
            col3val = 3;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, sql);
        }

        [TestMethod]
        public void TestPrimaryPlusUniqueMulti()
        {

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32), col3 int," +
                " PRIMARY KEY (col1), UNIQUE(col2) );";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 0;
            string col2val = "test1";
            int col3val = 4;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResult(resultIn, col1val, col2val, col3val);

            col1val = 1;
            col2val = "test6";
            col3val = 5;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);
            TestHelper.MatchSingleResultRow(resultIn, 1, col1val, col2val, col3val);


            col1val = 0;  // primary key violation
            col2val = "test7";
            col3val = 3;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, sql);

            col1val = 10;  // Unique key violation
            col2val = "test1";
            col3val = 3;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, sql);


            col1val = 0;  // Unique +Primary violation
            col2val = "test1";
            col3val = 3;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, sql);
        }

        [TestMethod]
        public void TestFK()
        {

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32), col3 int," +
                " PRIMARY KEY (col1) );";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            string table2 = TestHelper.GetNewTableName();
            string sql2 = "CREATE TABLE " + table2 + " (col1 int , col2 varchar(32), col3 int," +
                " PRIMARY KEY (col1), FOREIGN KEY (col3) REFERENCES "+table+" (col1) );";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql2);

            int col1val = 0;
            string col2val = "test1";
            int col3val = 4;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int rcol1val = 0;
            string rcol2val = "test1";
            int rcol3val = col1val; // correct reference
            sql = TestHelper.CreateStdInsert(table2, 3, rcol1val, rcol2val, rcol3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table2, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, rcol1val, rcol2val, rcol3val);


            rcol1val = 0;
            rcol2val = "test1";
            rcol3val = 7; // incorrect reference - should fail
            sql = TestHelper.CreateStdInsert(table2, 3, rcol1val, rcol2val, rcol3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, sql);

            //Test DELETE with FK
            sql = "DELETE FROM " + table;
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, sql); 

            sql = "DELETE FROM " + table2;
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = "DELETE FROM " + table;
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

       
        }

        [TestMethod]
        public void TestFKMulti()
        {

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32), col3 int," +
                " PRIMARY KEY (col1, col3) );";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            string table2 = TestHelper.GetNewTableName();
            string sql2 = "CREATE TABLE " + table2 + " (col1 int , col2 int , col3 varchar(32), " +
                "  FOREIGN KEY (col1,col2) REFERENCES " + table + " (col1,col3) );";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql2);

            int col1val = 0;
            string col2val = "test1";
            int col3val = 4;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int rcol1val = col1val;
            int rcol2val = col3val;// correct reference
            string rcol3val = "test3"; 
            sql = TestHelper.CreateStdInsert(table2, 3, rcol1val, rcol2val, rcol3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table2, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, rcol1val, rcol2val, rcol3val);


            rcol1val = col1val;
            rcol2val = 55; // incorrect reference - should fail
            rcol3val = "test3";        
            sql = TestHelper.CreateStdInsert(table2, 3, rcol1val, rcol2val, rcol3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, sql);

            //Test DELETE with FK
            sql = "DELETE FROM " + table;
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, sql);

            sql = "DELETE FROM " + table2;
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = "DELETE FROM " + table;
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

        }



        [TestMethod]
        public void TestFKCascadeDelete()
        {

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32), col3 int," +
                " PRIMARY KEY (col1) );";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            string table2 = TestHelper.GetNewTableName();
            string sql2 = "CREATE TABLE " + table2 + " (col1 int , col2 varchar(32), col3 int," +
                " PRIMARY KEY (col1), FOREIGN KEY (col3) REFERENCES " + table + " (col1) ON DELETE CASCADE );";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql2);

            int col1val = 0;
            string col2val = "test1";
            int col3val = 4;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int rcol1val = 0;
            string rcol2val = "test1";
            int rcol3val = col1val; // correct reference
            sql = TestHelper.CreateStdInsert(table2, 3, rcol1val, rcol2val, rcol3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table2, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, rcol1val, rcol2val, rcol3val);


            rcol1val = 0;
            rcol2val = "test1";
            rcol3val = 7; // incorrect reference - should fail
            sql = TestHelper.CreateStdInsert(table2, 3, rcol1val, rcol2val, rcol3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, sql);

            //Test DELETE with FK
            sql = "DELETE FROM " + table;
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = "SELECT * FROM " + table;
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 0);

            sql = "SELECT * FROM " + table2;
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 0);
        }


        [TestMethod]
        public void TestFKSetDefaultDelete()
        {

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32), col3 int," +
                " PRIMARY KEY (col1) );";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            string table2 = TestHelper.GetNewTableName();
            string sql2 = "CREATE TABLE " + table2 + " (col1 int , col2 varchar(32), col3 int DEFAULT 111," +
                " PRIMARY KEY (col1), FOREIGN KEY (col3) REFERENCES " + table + " (col1) ON DELETE SET DEFAULT );";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql2);

            int col1val = 0;
            string col2val = "test1";
            int col3val = 4;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int rcol1val = 0;
            string rcol2val = "test1";
            int rcol3val = col1val; // correct reference
            sql = TestHelper.CreateStdInsert(table2, 3, rcol1val, rcol2val, rcol3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table2, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, rcol1val, rcol2val, rcol3val);

 
            //Test DELETE with FK
            sql = "DELETE FROM " + table;
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            rcol1val = 0;
            rcol2val = "test1";
            rcol3val = 111;  // default value
            sql = TestHelper.CreateStdSelect(table2, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, rcol1val, rcol2val, rcol3val);

            sql = "SELECT * FROM " + table;
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 0);
        }


        [TestMethod]
        public void TestFKSetNullDelete()
        {

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32), col3 int," +
                " PRIMARY KEY (col1) );";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            string table2 = TestHelper.GetNewTableName();
            string sql2 = "CREATE TABLE " + table2 + " (col1 int , col2 varchar(32), col3 int ," +
                " PRIMARY KEY (col1), FOREIGN KEY (col3) REFERENCES " + table + " (col1) ON DELETE SET NULL );";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql2);

            int col1val = 0;
            string col2val = "test1";
            int col3val = 4;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int rcol1val = 0;
            string rcol2val = "test1";
            int rcol3val = col1val; // correct reference
            sql = TestHelper.CreateStdInsert(table2, 3, rcol1val, rcol2val, rcol3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table2, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, rcol1val, rcol2val, rcol3val);


            //Test DELETE with FK
            sql = "DELETE FROM " + table;
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

         
            sql = TestHelper.CreateStdSelect(table2, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, rcol1val, rcol2val, null);

            sql = "SELECT * FROM " + table;
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 0);
        }


        [TestMethod]
        public void TestFKCascadeUpdate()
        {

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32), col3 int," +
                " PRIMARY KEY (col1) );";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            string table2 = TestHelper.GetNewTableName();
            string sql2 = "CREATE TABLE " + table2 + " (col1 int , col2 varchar(32), col3 int," +
                " PRIMARY KEY (col1), FOREIGN KEY (col3) REFERENCES " + table + " (col1) ON UPDATE CASCADE );";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql2);

            int col1val = 0;
            string col2val = "test1";
            int col3val = 4;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int rcol1val = 0;
            string rcol2val = "test1";
            int rcol3val = col1val; // correct reference
            sql = TestHelper.CreateStdInsert(table2, 3, rcol1val, rcol2val, rcol3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table2, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, rcol1val, rcol2val, rcol3val);

            //Test UPDATE with FK
           
            sql = TestHelper.CreateStdUpdate(table, "col1", 5555);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            rcol1val = 0;
            rcol2val = "test1";
            rcol3val = 5555; // new value
            sql = TestHelper.CreateStdSelect(table2, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, rcol1val, rcol2val, rcol3val);
        
        }

        [TestMethod]
        public void TestFKNullUpdate()
        {

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32), col3 int," +
                " PRIMARY KEY (col1) );";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            string table2 = TestHelper.GetNewTableName();
            string sql2 = "CREATE TABLE " + table2 + " (col1 int , col2 varchar(32), col3 int," +
                " PRIMARY KEY (col1), FOREIGN KEY (col3) REFERENCES " + table + " (col1) ON UPDATE SET NULL );";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql2);

            int col1val = 0;
            string col2val = "test1";
            int col3val = 4;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int rcol1val = 0;
            string rcol2val = "test1";
            int rcol3val = col1val; // correct reference
            sql = TestHelper.CreateStdInsert(table2, 3, rcol1val, rcol2val, rcol3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table2, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, rcol1val, rcol2val, rcol3val);

            //Test UPDATE with FK

            sql = TestHelper.CreateStdUpdate(table, "col1", 5555);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            rcol1val = 0;
            rcol2val = "test1";
            rcol3val = 5555; // new value
            sql = TestHelper.CreateStdSelect(table2, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, rcol1val, rcol2val, null);

        }

        [TestMethod]
        public void TestFKDefaultUpdate()
        {

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32), col3 int," +
                " PRIMARY KEY (col1) );";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            string table2 = TestHelper.GetNewTableName();
            string sql2 = "CREATE TABLE " + table2 + " (col1 int , col2 varchar(32), col3 int DEFAULT 7777," +
                " PRIMARY KEY (col1), FOREIGN KEY (col3) REFERENCES " + table + " (col1) ON UPDATE SET DEFAULT );";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql2);

            int col1val = 0;
            string col2val = "test1";
            int col3val = 4;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

             col1val = 7777;
             col2val = "test1";
             col3val = 5;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int rcol1val = 0;
            string rcol2val = "test1";
            int rcol3val = col1val; // correct reference
            sql = TestHelper.CreateStdInsert(table2, 3, rcol1val, rcol2val, rcol3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table2, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, rcol1val, rcol2val, rcol3val);

            //Test UPDATE with FK

            sql = "UPDATE " + table + " SET col1= " + 55 + " WHERE col3= " + col3val;
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            rcol1val = 0;
            rcol2val = "test1";
            rcol3val = 7777; // new value
            sql = TestHelper.CreateStdSelect(table2, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, rcol1val, rcol2val, rcol3val);

        }


        [TestMethod]
        public void TestCheckConstraint()
        {

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32), col3 int," +
                " CHECK (col3 < 10) );";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
         

            int col1val = 0;
            string col2val = "test1";
            int col3val = 4; //chcek constraint okay
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, col1val, col2val, col3val);

            col1val = 7777;
            col2val = "test1";
            col3val = 55;  //chcek constraint vialation
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, sql);          

            ////Test Check constraints on updates
        
            col1val = 0;
            col2val = "test1";
            col3val = 9; // good value
            sql = TestHelper.CreateStdUpdate(table, "col3", col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, col1val, col2val, col3val);

            col3val = 10; // bad value
            sql = TestHelper.CreateStdUpdate(table, "col3", col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, sql);

        }



        [TestMethod]
        public void TestFKSelfReference()
        {
            Result resultIn;          

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32), col3 int," +
                " PRIMARY KEY (col1), FOREIGN KEY (col3) REFERENCES " + table + " (col1) );";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int col1val = 5;
            string col2val = "test1";
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, null);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int rcol1val = 10;
            string rcol2val = "test2";
            int rcol3val = 5; // correct reference
            sql = TestHelper.CreateStdInsert(table, 3, rcol1val, rcol2val, rcol3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);
            TestHelper.MatchSingleResultRow(resultIn, 1, rcol1val, rcol2val, rcol3val);


            rcol1val = 0;
            rcol2val = "test1";
            rcol3val = 7; // incorrect reference - should fail
            sql = TestHelper.CreateStdInsert(table, 3, rcol1val, rcol2val, rcol3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, sql);

        }


        [TestMethod]
        public void TestFKRestrictDropTable()
        {

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32), col3 int," +
                " PRIMARY KEY (col1) );";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            string table2 = TestHelper.GetNewTableName();
            string sql2 = "CREATE TABLE " + table2 + " (col1 int , col2 varchar(32), col3 int," +
                " PRIMARY KEY (col1), FOREIGN KEY (col3) REFERENCES " + table + " (col1)  );";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql2);

            int col1val = 0;
            string col2val = "test1";
            int col3val = 4;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int rcol1val = 0;
            string rcol2val = "test1";
            int rcol3val = col1val; // correct reference
            sql = TestHelper.CreateStdInsert(table2, 3, rcol1val, rcol2val, rcol3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
          

            //Test DROP with FK
            sql = "DROP TABLE " + table;
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, sql);

            sql = "DROP TABLE " + table +" RESTRICT";
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, sql);

            sql = "DROP TABLE " + table +" CASCADE";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            // both tables should be gone
            sql = TestHelper.CreateStdSelect(table, 3);
            TestHelper.ExecuteSelectSQL_Failure(sessionProxy, sql);

            sql = TestHelper.CreateStdSelect(table2, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);

                
        }

        [TestMethod]
        public void TestSetReferencialIntegrity()
        {

            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 varchar(32), col3 int," +
                " PRIMARY KEY (col1) );";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            string table2 = TestHelper.GetNewTableName();
            string sql2 = "CREATE TABLE " + table2 + " (col1 int , col2 varchar(32), col3 int," +
                " PRIMARY KEY (col1), FOREIGN KEY (col3) REFERENCES " + table + " (col1) );";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql2);

            int col1val = 0;
            string col2val = "test1";
            int col3val = 4;
            sql = TestHelper.CreateStdInsert(table, 3, col1val, col2val, col3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int rcol1val = 0;
            string rcol2val = "test1";
            int rcol3val = col1val; // correct reference
            sql = TestHelper.CreateStdInsert(table2, 3, rcol1val, rcol2val, rcol3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table2, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, rcol1val, rcol2val, rcol3val);


            rcol1val = 11;
            rcol2val = "test1";
            rcol3val = 7; // incorrect reference - should fail
            sql = TestHelper.CreateStdInsert(table2, 3, rcol1val, rcol2val, rcol3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, sql);

            //SET RI off
     
            sql = "SET REFERENTIAL_INTEGRITY FALSE ;" ;
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = TestHelper.CreateStdInsert(table2, 3, rcol1val, rcol2val, rcol3val);

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            sql = TestHelper.CreateStdSelect(table2, 3);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 2);
            TestHelper.MatchSingleResultRow(resultIn, 1, rcol1val, rcol2val, rcol3val);

            //SET RI On again
            sql = "SET REFERENTIAL_INTEGRITY TRUE ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            rcol1val = 13;
            rcol2val = "test4";
            rcol3val = 8; // incorrect reference - should fail
            sql = TestHelper.CreateStdInsert(table2, 3, rcol1val, rcol2val, rcol3val);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, sql);        


        }
    }
}
